using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Xml;
namespace Customs.Components.SXXK.ThanhKhoan
{
    /// <summary>
    /// Chua thong tin npl nhap con ton cua tung lan thanh ly cua to khai nhap
    /// </summary>
    public class ThongTinTonNPLNhap
    {
        /// <summary>
        /// Lay thong tin lien quan den so luong npl thanh ly cua mot lan thanh ly
        /// </summary>
        /// <param name="maHaiQuan"></param>
        /// <param name="maDoanhNghiep"></param>
        /// <param name="lanthanhly"></param>
        /// <returns></returns>
        public static DataSet GetTonNPLNhapOfLanThanhLy(string maHaiQuan, string maDoanhNghiep, int lanthanhly)
        {
            string connectionString = ConnectionManager.GetConnectionString("SXXK", maHaiQuan);
            string query ="SELECT     dbo.DNPLNHAP_TL.LAN_TL, dbo.DNPLNHAP_TL.MA_DV, dbo.DNPLNHAP_TL.SOTK, dbo.DNPLNHAP_TL.MA_LH, dbo.DNPLNHAP_TL.NAMDK, "+
                          "dbo.DNPLNHAP_TL.MA_HQ, dbo.DNPLNHAP_TL.MA_NPL, dbo.DNPLNHAP_TL.LUONG, dbo.DNPLNHAP_TL.TON_DAU, dbo.DNPLNHAP_TL.TON, "+
                          "dbo.DNPLNHAP.THUE_XNK, dbo.DNPLNHAP.THUE_TTDB, dbo.DNPLNHAP.THUE_VAT, dbo.DNPLNHAP.PHU_THU, dbo.DNPLNHAP.TRIGIA_THUKHAC, "+
                          "dbo.DNPLNHAP.LUONG AS LuongTK, dbo.DNPLNHAP.TON AS TonTK "+
                          "FROM         dbo.DNPLNHAP INNER JOIN "+
                          "dbo.DNPLNHAP_TL ON dbo.DNPLNHAP.SOTK = dbo.DNPLNHAP_TL.SOTK AND dbo.DNPLNHAP.MA_LH = dbo.DNPLNHAP_TL.MA_LH AND "+
                          "dbo.DNPLNHAP.NAMDK = dbo.DNPLNHAP_TL.NAMDK AND dbo.DNPLNHAP.MA_HQ = dbo.DNPLNHAP_TL.MA_HQ AND "+
                          "dbo.DNPLNHAP.MA_NPL = dbo.DNPLNHAP_TL.MA_NPL "+
                           "where dbo.DNPLNHAP_TL.LAN_TL=@LAN_TL and dbo.DNPLNHAP_TL.MA_DV=@Ma_DV and dbo.DNPLNHAP_TL.MA_HQ=@Ma_HQ";

            SqlParameter[] parameters = new SqlParameter[3];

            parameters[0] = new SqlParameter("@Ma_HQ", SqlDbType.Char);
            parameters[0].Size = 6;
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@Ma_DV", SqlDbType.VarChar);
            parameters[1].Size = 14;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maDoanhNghiep;

            parameters[2] = new SqlParameter("@LAN_TL", SqlDbType.Int);
            parameters[2].Direction = ParameterDirection.Input;
            parameters[2].Value = lanthanhly;

            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query, parameters);
            return ds;
            
        }
        /// <summary>
        /// Lay tat ca thong tin ve luong thanh ly cua mot don vi
        /// </summary>
        /// <param name="maHaiQuan"></param>
        /// <param name="maDoanhNghiep"></param>
        /// <returns></returns>
        public static DataSet GetTonNPLNhap(string maHaiQuan, string maDoanhNghiep)
        {
            string connectionString = ConnectionManager.GetConnectionString("SXXK", maHaiQuan);
            string query = "SELECT     dbo.DNPLNHAP_TL.LAN_TL, dbo.DNPLNHAP_TL.MA_DV, dbo.DNPLNHAP_TL.SOTK, dbo.DNPLNHAP_TL.MA_LH, dbo.DNPLNHAP_TL.NAMDK, " +
                             "dbo.DNPLNHAP_TL.MA_HQ, dbo.DNPLNHAP_TL.MA_NPL, dbo.DNPLNHAP_TL.LUONG, dbo.DNPLNHAP_TL.TON_DAU, dbo.DNPLNHAP_TL.TON, " +
                             "dbo.DNPLNHAP.THUE_XNK, dbo.DNPLNHAP.THUE_TTDB, dbo.DNPLNHAP.THUE_VAT, dbo.DNPLNHAP.PHU_THU, dbo.DNPLNHAP.TRIGIA_THUKHAC, " +
                             "dbo.DNPLNHAP.LUONG AS LuongTK, dbo.DNPLNHAP.TON AS TonTK " +
                             "FROM         dbo.DNPLNHAP INNER JOIN " +
                             "dbo.DNPLNHAP_TL ON dbo.DNPLNHAP.SOTK = dbo.DNPLNHAP_TL.SOTK AND dbo.DNPLNHAP.MA_LH = dbo.DNPLNHAP_TL.MA_LH AND " +
                             "dbo.DNPLNHAP.NAMDK = dbo.DNPLNHAP_TL.NAMDK AND dbo.DNPLNHAP.MA_HQ = dbo.DNPLNHAP_TL.MA_HQ AND " +
                             "dbo.DNPLNHAP.MA_NPL = dbo.DNPLNHAP_TL.MA_NPL " +
                              "where dbo.DNPLNHAP_TL.MA_DV=@Ma_DV and dbo.DNPLNHAP_TL.MA_HQ=@Ma_HQ";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@Ma_HQ", SqlDbType.Char);
            parameters[0].Size = 6;
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@Ma_DV", SqlDbType.VarChar);
            parameters[1].Size = 14;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maDoanhNghiep;

            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query, parameters);
            return ds;
        }
        /// <summary>
        /// Lay thong tin thanh ly thue cua mot lan thanh ly
        /// </summary>
        /// <param name="maHaiQuan"></param>
        /// <param name="maDoanhNghiep"></param>
        /// <param name="lanthanhly"></param>
        /// <returns></returns>
        public static DataSet GetTonThueNPLNhapOfLanThanhLy(string maHaiQuan, string maDoanhNghiep, int lanthanhly)
        {
            string connectionString = ConnectionManager.GetConnectionString("SXXK", maHaiQuan);
            string query ="SELECT     dbo.DNPLNHAP_TL_TH.LAN_TL, dbo.DNPLNHAP_TL_TH.MA_DV, dbo.DNPLNHAP_TL_TH.SOTK, dbo.DNPLNHAP_TL_TH.MA_LH, "+
                           "dbo.DNPLNHAP_TL_TH.NAMDK, dbo.DNPLNHAP_TL_TH.MA_HQ, dbo.DNPLNHAP_TL_TH.MA_NPL, dbo.DNPLNHAP_TL_TH.THUE_XNK, "+
                           "dbo.DNPLNHAP_TL_TH.THUE_VAT, dbo.DNPLNHAP_TL_TH.THUE_TTDB, dbo.DNPLNHAP_TL_TH.THUE_CLGIA, "+
                           "dbo.DNPLNHAP_TL_TH.TON_DAU_XNK, dbo.DNPLNHAP_TL_TH.TON_DAU_TTDB, dbo.DNPLNHAP_TL_TH.TON_DAU_VAT, "+
                           "dbo.DNPLNHAP_TL_TH.TON_DAU_CLGIA, dbo.DNPLNHAP_TL_TH.TON_XNK, dbo.DNPLNHAP_TL_TH.TON_TTDB, dbo.DNPLNHAP_TL_TH.TON_VAT, "+
                           "dbo.DNPLNHAP_TL_TH.TON_CLGIA, dbo.DNPLNHAP_TL_CT.LUONG AS LuongCT, dbo.DNPLNHAP_TL_CT.TON_DAU AS TonDau_CT, "+
                           "dbo.DNPLNHAP_TL_CT.TON AS TonCT,dbo.DNPLNHAP_TL_CT.MA_PHU, dbo.DNPLNHAP_TL_TH.LAN_DC " +
                           "FROM         dbo.DNPLNHAP_TL_CT INNER JOIN "+
                           "dbo.DNPLNHAP_TL_TH ON dbo.DNPLNHAP_TL_CT.LAN_TL = dbo.DNPLNHAP_TL_TH.LAN_TL AND "+
                           "dbo.DNPLNHAP_TL_CT.MA_DV = dbo.DNPLNHAP_TL_TH.MA_DV AND dbo.DNPLNHAP_TL_CT.SOTK = dbo.DNPLNHAP_TL_TH.SOTK AND "+
                           "dbo.DNPLNHAP_TL_CT.MA_LH = dbo.DNPLNHAP_TL_TH.MA_LH AND dbo.DNPLNHAP_TL_CT.NAMDK = dbo.DNPLNHAP_TL_TH.NAMDK AND "+
                           "dbo.DNPLNHAP_TL_CT.MA_HQ = dbo.DNPLNHAP_TL_TH.MA_HQ AND dbo.DNPLNHAP_TL_CT.MA_PHU = dbo.DNPLNHAP_TL_TH.MA_PHU "+
                            "where dbo.DNPLNHAP_TL_CT.LAN_TL=@LAN_TL and dbo.DNPLNHAP_TL_CT.MA_DV=@Ma_DV and dbo.DNPLNHAP_TL_CT.MA_HQ=@Ma_HQ";

            SqlParameter[] parameters = new SqlParameter[3];

            parameters[0] = new SqlParameter("@Ma_HQ", SqlDbType.Char);
            parameters[0].Size = 6;
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@Ma_DV", SqlDbType.VarChar);
            parameters[1].Size = 14;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maDoanhNghiep;

            parameters[2] = new SqlParameter("@LAN_TL", SqlDbType.Int);
            parameters[2].Direction = ParameterDirection.Input;
            parameters[2].Value = lanthanhly;

            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query, parameters);
            return ds;

        }
        /// <summary>
        /// lay thong tin ve thue cua doanh nghiep
        /// </summary>
        /// <param name="maHaiQuan"></param>
        /// <param name="maDoanhNghiep"></param>
        /// <returns></returns>
        public static DataSet GetTonThueNPLNhap(string maHaiQuan, string maDoanhNghiep)
        {
            string connectionString = ConnectionManager.GetConnectionString("SXXK", maHaiQuan);
            string query = "SELECT     dbo.DNPLNHAP_TL_TH.LAN_TL, dbo.DNPLNHAP_TL_TH.MA_DV, dbo.DNPLNHAP_TL_TH.SOTK, dbo.DNPLNHAP_TL_TH.MA_LH, " +
                               "dbo.DNPLNHAP_TL_TH.NAMDK, dbo.DNPLNHAP_TL_TH.MA_HQ, dbo.DNPLNHAP_TL_TH.MA_NPL, dbo.DNPLNHAP_TL_TH.THUE_XNK, " +
                               "dbo.DNPLNHAP_TL_TH.THUE_VAT, dbo.DNPLNHAP_TL_TH.THUE_TTDB, dbo.DNPLNHAP_TL_TH.THUE_CLGIA, " +
                               "dbo.DNPLNHAP_TL_TH.TON_DAU_XNK, dbo.DNPLNHAP_TL_TH.TON_DAU_TTDB, dbo.DNPLNHAP_TL_TH.TON_DAU_VAT, " +
                               "dbo.DNPLNHAP_TL_TH.TON_DAU_CLGIA, dbo.DNPLNHAP_TL_TH.TON_XNK, dbo.DNPLNHAP_TL_TH.TON_TTDB, dbo.DNPLNHAP_TL_TH.TON_VAT, " +
                               "dbo.DNPLNHAP_TL_TH.TON_CLGIA, dbo.DNPLNHAP_TL_CT.LUONG AS LuongCT, dbo.DNPLNHAP_TL_CT.TON_DAU AS TonDau_CT, " +
                               "dbo.DNPLNHAP_TL_CT.TON AS TonCT,dbo.DNPLNHAP_TL_CT.MA_PHU, dbo.DNPLNHAP_TL_TH.LAN_DC " +
                               "FROM         dbo.DNPLNHAP_TL_CT INNER JOIN " +
                               "dbo.DNPLNHAP_TL_TH ON dbo.DNPLNHAP_TL_CT.LAN_TL = dbo.DNPLNHAP_TL_TH.LAN_TL AND " +
                               "dbo.DNPLNHAP_TL_CT.MA_DV = dbo.DNPLNHAP_TL_TH.MA_DV AND dbo.DNPLNHAP_TL_CT.SOTK = dbo.DNPLNHAP_TL_TH.SOTK AND " +
                               "dbo.DNPLNHAP_TL_CT.MA_LH = dbo.DNPLNHAP_TL_TH.MA_LH AND dbo.DNPLNHAP_TL_CT.NAMDK = dbo.DNPLNHAP_TL_TH.NAMDK AND " +
                               "dbo.DNPLNHAP_TL_CT.MA_HQ = dbo.DNPLNHAP_TL_TH.MA_HQ AND dbo.DNPLNHAP_TL_CT.MA_PHU = dbo.DNPLNHAP_TL_TH.MA_PHU " +
                    "where dbo.DNPLNHAP_TL_CT.MA_DV=@Ma_DV and dbo.DNPLNHAP_TL_CT.MA_HQ=@Ma_HQ";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@Ma_HQ", SqlDbType.Char);
            parameters[0].Size = 6;
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@Ma_DV", SqlDbType.VarChar);
            parameters[1].Size = 14;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maDoanhNghiep;

            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query, parameters);
            return ds;
        }

        ///// <summary>
        ///// Phuong thuc chinh tra ve thong tin thanh ly 
        ///// cua mot lan thanh ly
        ///// </summary>
        ///// <param name="maHaiQuan"></param>
        ///// <param name="maDoanhNghiep"></param>
        ///// <param name="lanthanhly"></param>
        ///// <returns></returns>
        //public static string GetLuongNPLANDThueTonOfLanThanhLy(string maHaiQuan, string maDoanhNghiep, int lanthanhly)
        //{
        //    //XmlDocument docLuong = new XmlDocument();
        //    //string stLuong = GetTonNPLNhapOfLanThanhLy(maHaiQuan, maDoanhNghiep, lanthanhly);
        //    //string stThue = GetTonThueNPLNhapOfLanThanhLy(maHaiQuan, maDoanhNghiep, lanthanhly);
        //    //if (stLuong == null)
        //    //    return null;
        //    //docLuong.LoadXml(stLuong);
        //    //if (docLuong.InnerText == "")
        //    //    return null;
        //    //XmlDocument docThue = new XmlDocument();
        //    //docThue.LoadXml(stThue);
        //    ////tao nut goc cho van ban
        //    //XmlDocument doc = new XmlDocument();
        //    //XmlElement root = doc.CreateElement("Root");
        //    //doc.AppendChild(root);
        //    ////dua cac nut con la cac dataset vao
        //    //XmlElement eLuong = doc.CreateElement("NewDataSet");
        //    //XmlElement eThue = doc.CreateElement("NewDataSet");
        //    //eLuong.InnerXml = docLuong.FirstChild.InnerXml;
        //    //eThue.InnerXml = docThue.FirstChild.InnerXml;
        //    //root.AppendChild(eLuong);
        //    //root.AppendChild(eThue);
        //    //return doc.InnerXml;
        //    return "doc.InnerXml";
        //}

        ///// <summary>
        ///// Lay thong tin thanh ly cua doanh nghiep
        ///// </summary>
        ///// <param name="maHaiQuan"></param>
        ///// <param name="maDoanhNghiep"></param>
        ///// <returns></returns>
        //public static string GetLuongNPLANDThueTon(string maHaiQuan, string maDoanhNghiep)
        //{
        //    XmlDocument docLuong = new XmlDocument();
        //    string stLuong = GetTonNPLNhap(maHaiQuan, maDoanhNghiep);
        //    string stThue = GetTonThueNPLNhap(maHaiQuan, maDoanhNghiep);
        //    docLuong.LoadXml(stLuong);
        //    if (docLuong.InnerText == "")
        //        return null;
        //    XmlDocument docThue = new XmlDocument();
        //    docThue.LoadXml(stThue);
        //    //tao nut goc cho van ban
        //    XmlDocument doc = new XmlDocument();
        //    XmlElement root = doc.CreateElement("Root");
        //    doc.AppendChild(root);
        //    //dua cac nut con la cac dataset vao
        //    XmlElement eLuong = doc.CreateElement("NewDataSet");
        //    XmlElement eThue = doc.CreateElement("NewDataSet");
        //    eLuong.InnerXml = docLuong.FirstChild.InnerXml;
        //    eThue.InnerXml = docThue.FirstChild.InnerXml;
        //    root.AppendChild(eLuong);
        //    root.AppendChild(eThue);
        //    return doc.InnerXml;
        //}

    }
}
